﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class DBhelper
    {
        private static readonly string connstr = ConfigurationManager.ConnectionStrings["add"].ConnectionString;
        private static SqlConnection conn = new SqlConnection(connstr);
        /// <summary>
        /// 查询
        /// </summary>
        public static SqlDataReader ExecuteReaders(string sql, params SqlParameter[] paras)
        {
            //连接数据库
            SqlConnection conn = new SqlConnection(connstr);
            //打开连接
            conn.Open();
            //执行sql命令
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (paras != null && paras.Length > 0)
            {
                cmd.Parameters.AddRange(paras);
            }
            //执行查询
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public static int executenonquery(string sql, params SqlParameter[] pars)
        {
            SqlCommand cm = new SqlCommand(sql, conn);//执行命令
            //添加参数
            if (pars != null && pars.Length > 0)
            {
                cm.Parameters.AddRange(pars);
            }
            //打开数据库连接
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            //执行命令
            return cm.ExecuteNonQuery();
        }

        public static DataTable GetDataTable(string sqlstr)
        {
            //执行SQL语句
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            //创建适配器
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            //创建DataTable内存表
            DataTable dt = new DataTable();
            //将适配好的结果填充到DataTable中
            sda.Fill(dt);
            //返回DataTable类型的结果集
            return dt;
        }
        /// <summary>
        /// 增删改操作
        /// </summary>
        /// <param name="sqlstr">要执行的SQL语句</param>
        /// <returns>返回受影响行数</returns>
        public static int GetExecuteNonQuery(string sqlstr)
        {
            //执行SQL语句
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            //判断链接是否已打开，若关闭立即打开
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            //返回受影响行数
            int r = cmd.ExecuteNonQuery();
            //判断链接是否已关闭，若打开立即关闭
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
            //返回受影响行数
            return r;
        }
    }
}
